package com.knife.util;

import org.apache.poi.ss.usermodel.*;

import java.io.File;
import java.io.FileWriter;
import java.text.SimpleDateFormat;
import java.time.Instant;
import java.util.Date;

/**
 * create by yongli on 2021-12-23 14:21
 */

public class FarmUpdateUtil {
    public static void main(String[] args) {


        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        SimpleDateFormat sdfShort = new SimpleDateFormat("yyyy-MM-dd");
        String path = "/Users/yongli/Desktop/风场出质保.xlsx";
        String path2 = "/Users/yongli/Desktop/1229-test.txt";
        int count = 0;
        try {

            FileWriter fileWriter = new FileWriter(path2);
            Workbook workbook = WorkbookFactory.create(new File(path));
            Sheet sheet = workbook.getSheetAt(0);

            //
            int len = sheet.getPhysicalNumberOfRows();
            System.out.println("len:" + len);

            String template2 = "update  sany_heavy_energy.wf_farm ";

            for (int i = 1; i < len; i++) {
                Row row = sheet.getRow(i);
                int width = row.getLastCellNum();
//                for (int j = 0; j < width; j++) {
//                    if (row.getCell(j) != null && j != 1) {
//                        row.getCell(j).setCellType(Cell.CELL_TYPE_STRING);
//                    }
//                }
//                row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
//                System.out.println(width);
//                System.out.println(row.getCell(3).getStringCellValue());
//                System.out.println(template2);
                String template = template2;
                for (int j = 0; j < width; j++) {
                    if(row.getCell(j)==null){
//                        System.out.println(3);
                        continue;
                    }

                    // 逐个字段进行解析,是定制化工具
                    switch (j) {
                        case 3: {
                            String code = row.getCell(j).getStringCellValue();
                            template +=" where code =\'"+ code + "\';";
                            break;
                        }
                        case 0: {
                            String str = row.getCell(j).getStringCellValue();
                            Instant OUT_WARRANTY_THEORY_TIME = Instant.parse(str);
                            if(OUT_WARRANTY_THEORY_TIME!=null){
                                if(!template.contains("set")){
                                    template += " set OUT_WARRANTY_THEORY_TIME =\'" +
                                            sdf.format(Date.from(OUT_WARRANTY_THEORY_TIME))+"\'";
                                }else {
                                    template += ", OUT_WARRANTY_THEORY_TIME =\'" +
                                            sdf.format(Date.from(OUT_WARRANTY_THEORY_TIME))+"\'";
                                }
                            }
                            break;
                        }
                        case 1: {

                            String str = row.getCell(j).getStringCellValue();
                            Date date = sdfShort.parse(str);

                            if(date!=null){
                                if(!template.contains("set")){
                                    template += "set OUT_WARRANTY_ACTUAL_TIME =\'" +
                                            sdf.format(date)+"\'";
                                }else {
                                    template += ", OUT_WARRANTY_ACTUAL_TIME =\'" +
                                            sdf.format(date)+"\'";
                                }
                            }
                            break;

                        }
                        case 2: {
                            Double WARRANTY_MONTHS = row.getCell(j).getNumericCellValue();

                            if(WARRANTY_MONTHS!=null){
                                if(!template.contains("set")){
                                    template += " set WARRANTY_MONTHS=" + WARRANTY_MONTHS;
                                }else {
                                    template += ", WARRANTY_MONTHS=" + WARRANTY_MONTHS;
                                }
                            }
                            break;

                        }
                    }
                }
                System.out.println(template);
            }
            System.out.println(count);
        } catch (
                Exception e) {
            e.printStackTrace();
        } finally {
            System.out.println(count);
        }
    }
}
